/**
 * Copyright (c) 2019 Coder League
 * All rights reserved.
 *
 * File：UserDaoImpl.java
 * History:
 *         2019年5月8日: Initially created, Chrise.
 */
package club.coderleague.ilsp.dao.impl;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import club.coderleague.data.jpa.domain.Page;
import club.coderleague.data.jpa.id.support.SnowflakeV4;
import club.coderleague.data.jpa.repository.support.AbstractDataRepositoryExtension;
import club.coderleague.ilsp.common.domain.beans.PagingParameters;
import club.coderleague.ilsp.common.domain.beans.UserSession;
import club.coderleague.ilsp.common.domain.beans.UsersExtension;
import club.coderleague.ilsp.common.domain.enums.EntityState;
import club.coderleague.ilsp.common.domain.enums.UserGroup;
import club.coderleague.ilsp.common.domain.enums.UserType;
import club.coderleague.ilsp.dao.extension.UserDaoExtension;
import club.coderleague.ilsp.entities.Users;

/**
 * 用户数据访问对象实现。
 * @author Chrise
 */
public class UserDaoImpl extends AbstractDataRepositoryExtension<Users, Long, SnowflakeV4> implements UserDaoExtension {
	/**
	 * @see club.coderleague.ilsp.dao.extension.UserDaoExtension#queryAdministrator()
	 */
	@Override
	public Users queryAdministrator() {
		String jpql = "from Users where entitystate = ?0 and usertype = ?1";
		return super.queryEntity(jpql, EntityState.VALID.getValue(), UserType.ADMIN.getValue());
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.UserDaoExtension#isAdministratorExists()
	 */
	@Override
	public boolean isAdministratorExists() {
		String jpql = "select count(entityid) from Users where entitystate = ?0 and usertype = ?1";
		Long count = super.queryLong(jpql, EntityState.VALID.getValue(), UserType.ADMIN.getValue());
		return (count > 0);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.UserDaoExtension#queryUserSession(java.lang.String, boolean)
	 */
	@Override
	public UserSession queryUserSession(String loginname, boolean isphone) {
		String search = (isphone) ? "u.phonehash = ?1" : "u.loginname = ?1";
		String jpql = "select u.entityid as userid, u.loginpassword as password, " 
			+ "o.orgname as orgname, u.username as username, u.usertype as usertype, u.loginname as loginname from Users u " 
			+ "left join Organizations o on u.orgid = o.entityid where u.entitystate = ?0 and " + search;
		
		UserSession us = super.queryCustomBean(UserSession.class, jpql, EntityState.VALID.getValue(), loginname);
		if (us != null) us.setUsergroup(UserGroup.MANAGER.getValue());
		
		return us;
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.UserDaoExtension#queryAuthorities(java.lang.Long)
	 */
	@Override
	public List<String> queryAuthorities(Long userid) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("entitystate", EntityState.VALID.getValue());
		
		String table = "", where = "";
		if (userid != null) {
			table = ", Roleauths r, Userauths u";
			where = " and f.entityid = r.funcid and r.roleid = u.roleid and u.userid = :userid and r.entitystate = :entitystate and u.entitystate = :entitystate";
			params.put("userid", userid);
		}
		String jpql = "select f.authorityid from Functions f" + table + " where f.authorityid is not null and f.authorityid != '' and f.entitystate = :entitystate" + where;
		return super.queryStringList(jpql, params);
	}

	@Override
	public Page<UsersExtension> findPageByParamsMap(PagingParameters pagingparameters, boolean isrecycle, String keyword, String username, String userphone) {
		StringBuilder qsqlBuilder = new StringBuilder("select u.entityid as entityid, u.creator as creator, u.createtime as createtime, u.modifier as modifier,"
			+ " u.modifytime as modifytime, u.entitystate as entitystate, u.orgid as orgid, u.username as username, u.userphone as userphone,"
			+ " u.phonehash as phonehash, u.loginname as loginname, u.loginpassword as loginpassword, u.usertype as usertype, o.orgname as orgname,"
			+ " ra.rolenames as rolenames"
			+ " from users u"
			+ " left join organizations o on o.entityid = u.orgid and o.entitystate = :validstate"
			+ " left join (select group_concat(r.entityid) as roleids, group_concat(r.rolename) as rolenames, ua.userid as userid from roles r"
			+ " inner join userauths ua on ua.roleid = r.entityid and ua.entitystate = :validstate"
			+ " where r.entitystate = :validstate group by ua.userid) ra on ra.userid = u.entityid"
			+ " where u.usertype = :normalusertype and u.entitystate in (:entitystate)");
		StringBuilder csqlBuilder = new StringBuilder("select count(*) from users u"
			+ " left join organizations o on o.entityid = u.orgid and o.entitystate = :validstate"
			+ " left join (select group_concat(r.entityid) as roleids, group_concat(r.rolename) as rolenames, ua.userid as userid from roles r"
			+ " inner join userauths ua on ua.roleid = r.entityid and ua.entitystate = :validstate"
			+ " where r.entitystate = :validstate group by ua.userid) ra on ra.userid = u.entityid"
			+ " where u.usertype = :normalusertype and u.entitystate in (:entitystate)");
		Map<String, Object> params = new HashMap<>();
		params.put("normalusertype", UserType.NORMAL.getValue());
		params.put("validstate", EntityState.VALID.getValue());
		// 回收站
		if (!isrecycle) {
			params.put("entitystate", Arrays.asList(EntityState.VALID.getValue(), EntityState.FROZEN.getValue()));
		} else {
			params.put("entitystate", Arrays.asList(EntityState.INVALID.getValue()));
		}
		// 用户名
		if (StringUtils.isNotBlank(username)) {
			qsqlBuilder.append(" and u.username = :username");
			csqlBuilder.append(" and u.username = :username");
			params.put("username", username);
		}
		// 用户电话
		if (StringUtils.isNotBlank(userphone)) {
			qsqlBuilder.append(" and u.userphone = :userphone");
			csqlBuilder.append(" and u.userphone = :userphone");
			params.put("userphone", userphone);
		}
		// 关键字
		if (StringUtils.isNotBlank(keyword)) {
			qsqlBuilder.append(" and (u.loginname like :keyword or o.orgname like :keyword or ra.rolenames like :keyword)");
			csqlBuilder.append(" and (u.loginname like :keyword or o.orgname like :keyword or ra.rolenames like :keyword)");
			params.put("keyword", "%" + keyword + "%");
		}
		// 排序
		qsqlBuilder.append(" order by ");
		if (StringUtils.isNoneBlank(pagingparameters.getSortField(), pagingparameters.getSortOrder())) {
			qsqlBuilder.append(pagingparameters.getSortField()).append(" ").append(pagingparameters.getSortOrder());
		} else {
			qsqlBuilder.append("createtime desc");
		}
		return super.queryCustomBeanPageBySql(UsersExtension.class, csqlBuilder.toString(), qsqlBuilder.toString(), pagingparameters.getPageIndex(), pagingparameters.getPageSize(), params);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.UserDaoExtension#existsByPhonehashOrLoginnameNotEntityid(java.lang.String, java.lang.String, java.lang.Long)
	 */
	@Override
	public boolean existsByPhonehashOrLoginnameNotEntityid(String phonehash, String loginname, Long entityid) {
		StringBuilder sqlBuilder = new StringBuilder("select count(*) from users u where u.entitystate in (:validstate)");
		Map<String, Object> params = new HashMap<>();
		params.put("validstate", Arrays.asList(EntityState.VALID.getValue(), EntityState.FROZEN.getValue()));
		params.put("phonehash", phonehash);
		if (StringUtils.isNotBlank(loginname)) {
			sqlBuilder.append(" and (u.phonehash = :phonehash or u.loginname = :loginname)");
			params.put("loginname", loginname);
		} else {
			sqlBuilder.append(" and u.phonehash = :phonehash");
		}
		if (entityid != null) {
			sqlBuilder.append(" and u.entityid != :entityid");
			params.put("entityid", entityid);
		}
		return super.queryLongBySql(sqlBuilder.toString(), params) > 0;
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.UserDaoExtension#findExtensionByEntityid(java.lang.Long)
	 */
	@Override
	public UsersExtension findExtensionByEntityid(Long entityid) {
		String sql = "select u.entityid as entityid, u.orgid as orgid, u.username as username, u.userphone as userphone, u.loginname as loginname,"
				+ " o.orgname as orgname, ra.roleids as roleids, ra.rolenames as rolenames"
				+ " from users u"
				+ " left join organizations o on o.entityid = u.orgid and o.entitystate = ?1"
				+ " left join (select group_concat(r.entityid) as roleids, group_concat(r.rolename) as rolenames, ua.userid as userid from roles r"
				+ " inner join userauths ua on ua.roleid = r.entityid and ua.entitystate = ?1"
				+ " where r.entitystate = ?1 group by ua.userid) ra on ra.userid = u.entityid"
				+ " where u.entityid = ?0";
		return super.queryCustomBeanBySql(UsersExtension.class, sql, entityid, EntityState.VALID.getValue());
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.UserDaoExtension#findAllUsernameAndUserphone(boolean)
	 */
	@Override
	public List<UsersExtension> findAllUsernameAndUserphone(boolean isrecycle) {
		String sql = "select u.entityid as entityid, u.username as username, u.userphone as userphone"
				+ " from users u"
				+ " where u.entitystate in (:entitystate) and u.usertype = :normalusertype";
		Map<String, Object> params = new HashMap<>();
		params.put("normalusertype", UserType.NORMAL.getValue());
		if (!isrecycle) {
			params.put("entitystate", Arrays.asList(EntityState.VALID.getValue(), EntityState.FROZEN.getValue()));
		} else {
			params.put("entitystate", Arrays.asList(EntityState.INVALID.getValue()));
		}
		return super.queryCustomBeanListBySql(UsersExtension.class, sql, params);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.UserDaoExtension#existsByLoginnameNotEntityid(java.lang.String, java.lang.Long)
	 */
	@Override
	public boolean existsByLoginnameNotEntityid(String loginname, Long entityid) {
		StringBuilder sqlBuilder = new StringBuilder("select count(*) from users u where u.entitystate in (:validstate)");
		Map<String, Object> params = new HashMap<>();
		params.put("validstate", Arrays.asList(EntityState.VALID.getValue(), EntityState.FROZEN.getValue()));
		if (StringUtils.isNotBlank(loginname)) {
			sqlBuilder.append(" and u.loginname = :loginname");
			params.put("loginname", loginname);
		}
		if (entityid != null) {
			sqlBuilder.append(" and u.entityid != :entityid");
			params.put("entityid", entityid);
		}
		return super.queryLongBySql(sqlBuilder.toString(), params) > 0;
	}
}
